﻿using NPOI;
using NPOI.HSSF.UserModel;
using NPOI.SS.UserModel;
using System;
using System.Collections.Generic;
using System.Collections.ObjectModel;
using System.IO;
using System.Linq;
using System.Text;
using System.Threading.Tasks;

namespace CANDataIO
{

    public interface IExcel
    {
        void Save(string FileName, System.Data.DataTable data, string SheetName,bool readOnly);
        void Load(string FileName, out System.Data.DataTable data, string SheetName, bool ShowLog = false);
    }

    /// <summary>
    /// .xls
    /// </summary>
    public class Excel2007 : ExcelBase
    {

        public void Save(string FileName, List<string> Headers, Collection<(DateTime time, ArraySegment<decimal> ds)> data, string SheetName, Action<double> CB,bool readOnly)
        {
            HSSFWorkbook wk = new HSSFWorkbook();//这是用于后缀名是.xls的excel文件的操作
            ISheet isheet = wk.CreateSheet(SheetName);//这是创建一个工作簿，

            IRow row;
            ICell cell;
            int rowIndex = 0;
            int cellIndex = 0;
            int RowCount = data.Count;
            int ColumnCount = Headers.Count;

            row = isheet.CreateRow(rowIndex);//这个函数是创建该工作簿的第rowIndex行，并不是创建rowIndex行，从第0行开始   
            for (cellIndex = 0; cellIndex < ColumnCount; cellIndex++)
            {
                cell = row.CreateCell(cellIndex);//这个函数是创建该工作簿第cellIndex列（即某个单元格），从第0列开始
                var cellData = (string)Headers[cellIndex];
                cell.SetCellValue(cellData);//这个函数就是向第rowIndex行和第cellIndex列放数据
            }
            rowIndex++;
            if (CB != null)
            {
                CB(0.01);
            }
            for (int dataIndex = 0; dataIndex < RowCount; dataIndex++)
            {
                row = isheet.CreateRow(rowIndex);//这个函数是创建该工作簿的第rowIndex行，并不是创建rowIndex行，从第0行开始  
                rowIndex++;
                cell = row.CreateCell(0);
                var cellData = data[dataIndex].time.ToString();
                cell.SetCellValue(cellData);//这个函数就是向第rowIndex行和第cellIndex列放数据

                for (cellIndex = 0; cellIndex < ColumnCount - 1; cellIndex++)
                {
                    cell = row.CreateCell(cellIndex + 1);//这个函数是创建该工作簿第cellIndex列（即某个单元格），从第0列开始
                    cellData = data[dataIndex].ds.Array[cellIndex].ToString();
                    cell.SetCellValue(cellData);//这个函数就是向第rowIndex行和第cellIndex列放数据
                }

                if (CB != null)
                {
                    CB((dataIndex / ((double)RowCount)) - 0.01);
                }
            }
            CB(0.99);
            FileStream fs2 = new FileStream(FileName, FileMode.OpenOrCreate);
            wk.Write(fs2);
            wk.Close();//这就是释放资源
            fs2.Close();
            fs2.Dispose();
            SetFileReadAccess(FileName, readOnly);
            CB(1);
        }

        public void Save(string FileName, List<string> Headers, Collection<List<string>> data, string SheetName, Action<double> CB,bool readOnly)
        {
            HSSFWorkbook wk = new HSSFWorkbook();//这是用于后缀名是.xls的excel文件的操作
            ISheet isheet = wk.CreateSheet(SheetName);//这是创建一个工作簿，

            SaveBaseSub(isheet, Headers, data, CB,null);

            if (CB != null)
                CB(0.99);
            FileStream fs2 = new FileStream(FileName, FileMode.OpenOrCreate);
            wk.Write(fs2);
            wk.Close();//这就是释放资源
            fs2.Close();
            fs2.Dispose();
            SetFileReadAccess(FileName, readOnly);
            if (CB != null)
                CB(1);
        }


        private void SaveBaseSub(ISheet isheet, List<string> Headers, Collection<List<string>> data, Action<double> CB, string DevSheetTitle)
        {
            IRow row;
            ICell cell;
            int rowIndex = 0;

            if (DevSheetTitle != null)
            {
                row = isheet.CreateRow(rowIndex);  
                cell = row.CreateCell(0);
                cell.SetCellValue(DevSheetTitle);
                //cell.CellStyle.FillForegroundColor = 64;
                rowIndex++;
            }

            int cellIndex = 0;
            int RowCount = data.Count;
            int ColumnCount = Headers.Count;

            row = isheet.CreateRow(rowIndex);//这个函数是创建该工作簿的第rowIndex行，并不是创建rowIndex行，从第0行开始   
            for (cellIndex = 0; cellIndex < ColumnCount; cellIndex++)
            {
                cell = row.CreateCell(cellIndex);//这个函数是创建该工作簿第cellIndex列（即某个单元格），从第0列开始
                var cellData = (string)Headers[cellIndex];
                cell.SetCellValue(cellData);//这个函数就是向第rowIndex行和第cellIndex列放数据
            }
            rowIndex++;
            if (CB != null)
            {
                CB(0.01);
            }
            for (int dataIndex = 0; dataIndex < RowCount; dataIndex++)
            {
                row = isheet.CreateRow(rowIndex);//这个函数是创建该工作簿的第rowIndex行，并不是创建rowIndex行，从第0行开始  
                rowIndex++;
                for (cellIndex = 0; cellIndex < ColumnCount; cellIndex++)
                {
                    cell = row.CreateCell(cellIndex);//这个函数是创建该工作簿第cellIndex列（即某个单元格），从第0列开始
                    var cellData = data[dataIndex][cellIndex];
                    cell.SetCellValue(cellData);//这个函数就是向第rowIndex行和第cellIndex列放数据
                }

                if (CB != null)
                {
                    CB((dataIndex / ((double)RowCount)) - 0.01);
                }
            }
        }


        public override void Save(string FileName, System.Data.DataTable data, string SheetName, bool readOnly)
        {
            HSSFWorkbook wk = new HSSFWorkbook();//这是用于后缀名是.xls的excel文件的操作
            ISheet isheet = wk.CreateSheet(SheetName);//这是创建一个工作簿，

            SaveBaseSub(isheet, data);

            FileStream fs2 = new FileStream(FileName, FileMode.OpenOrCreate);
            wk.Write(fs2);
            wk.Close();//这就是释放资源
            fs2.Close();
            fs2.Dispose();
            SetFileReadAccess(FileName, readOnly);
        }

        public void Save(string FileName, System.Data.DataTable data, string SheetName,
            string SheetDevs, List<string> Headers, Collection<List<string>> data2, string DevSheetTitle,bool readOnly)
        {
            HSSFWorkbook wk = new HSSFWorkbook();//这是用于后缀名是.xls的excel文件的操作
            ISheet isheet = wk.CreateSheet(SheetName);//这是创建一个工作簿，

            SaveBaseSub(isheet, data);
            ISheet isheet2 = wk.CreateSheet(SheetDevs);//这是创建一个工作簿，

            SaveBaseSub(isheet2, Headers, data2, null, DevSheetTitle);

            FileStream fs2 = new FileStream(FileName, FileMode.OpenOrCreate);
            wk.Write(fs2);
            wk.Close();//这就是释放资源
            fs2.Close();
            fs2.Dispose();
            SetFileReadAccess(FileName, readOnly);
        }

        public override void Load(string FileName, out System.Data.DataTable data, string SheetName, bool ShowLog = false)
        {
            data = null;
            if (System.IO.File.Exists(FileName) == false)
            {
                return;
            }
            if (Path.GetExtension(FileName.ToLower()) != ".xls")
            {
                return;
            }

            using (FileStream fs = File.OpenRead(FileName))
            {
                IWorkbook workbook = null;
                workbook = new HSSFWorkbook(fs);

                //因为Excel表中可能不止一个工作簿，这里为了演示，我们遍历所有工作簿
                //同时要注意excel的工作簿的对应关系，其工作簿也是从0开始
                //工作簿位置为0时，其在excel表的最左边，参考下面图一

                ISheet sheet = workbook.GetSheet(SheetName);
                if (sheet == null)
                    return;

                data = new System.Data.DataTable(SheetName);
                LoadBaseSub(sheet, data, ShowLog);

            }// End using

        }
    }
}
